#
#  This library provides the functions to become a basic access to the data 
#  stored in a SQLite3 (http://www.sqlite.org) database file in a script.
#
#  Synopsis:
#		sqlite_set_db FILE
#		sqlite_get_tables
#		sqlite_get_columns TABLE
#		sqlite_insert TABLE PREFIX
#		sqlite_update TABLE COLUMN VALUE WHERE
#		sqlite_func TABLE FUNC PREFIX ORDER
#		sqlite_func_where TABLE FUNC PREFIX WHERE ORDER
#		sqlite_row TABLE WHERE PREFIX
#		sqlite_cvt_create TABLE
#		sqlite_cvt_set TABLE VNAME VALUE
#		sqlite_cvt_read TABLE PREFIX
#
# depends on sqlite3

# =============================================================================
# DEFAULT LIBRARY VARIABLES
# =============================================================================

# Set this variable to 1 to get more information.
VERBOSE=''

# Here you can define the SQLite file. You should leave this variable empty,
# and make use of the sqlite_set_db function.
SQLFILE=''

# =============================================================================
# INTERNAL FUNCTIONS
# =============================================================================

# Check if sql database file is defined.
___check_db (){
	if [ -z $SQLFILE ]; then
		echo "(sqlite.sh) ERROR: No DB file defined!" >&2
		exit 1
	fi
}

# Returns a 'cleaned' list.
___cll (){
	echo $1 | sed -e 's/[[:space:]]\+/ /g'
}

# Define a variable.
# $1: variable name (key)
# $2: value
___def (){
	eval "$1='$2'"
}

# Unset a variable.
# $1: variable name (key)
___uset (){
	eval "unset $1"
}

# Get an element/word from a list of strings.
# $1: list
# $2: position
___sel (){
	echo "$1" | cut -d' ' -f$2
}

# Define variables given by a list of
# variable keys and values.
# $1: list of keys
# $2: list of values
# $3: prefix (optional)
___deflist (){
	local k v
	for i in $( seq 1 $(echo $1| wc -w) ); do
		k=$(___sel "$1" $i)
		v=$(___sel "$2" $i)
		[ ! "$3" = '' ] && k="$3_$k"
		___def "$k" "$v"
	done
}

# Unset variables given by a list of
# variable keys and values.
# $1: list of keys
# $2: prefix (optional)
___unsetlist (){
	local k
	for i in $( seq 1 $(echo $1| wc -w) ); do
		k=$(___sel "$1" $i)
		[ ! "$2" = '' ] && k="$2_$k"
		___def "$k" $v
	done
}

# =============================================================================
# USER FUNCTIONS
# =============================================================================

# BASIC FUNCTIONS -------------------------------------------------------------

# Set the sql data file.
# $1: file
set_db (){
	local h
	if [ ! -f $1 ]; then
		echo "(sqlite.sh) ERROR: File \'$1\' did not exist!" >&2
		exit 1
	elif [ ! -s $1 ]; then
		echo "(sqlite.sh) ERROR: File \'$1\' is empty!" >&2
		exit 1
	fi
	h=$(sqlite3 $1 "pragma integrity_check;" 2>&1)
	if [ "$h" != "ok" ]; then
		echo "(sqlite.sh) ERROR: File \'$1\' is not valid!" >&2
		echo "(sqlite.sh) -> $h" >&2
		exit 1
	else
		SQLFILE="$1"
		[ $VERBOSE ] && echo "(sqlite.sh) INFO: DB file set to \'$1\'!" >&2
	fi
}

# Returns the list of existing tables.
get_tables (){
	local out
	___check_db
	out=$(sqlite3 $SQLFILE ".tables")
	echo $out
}

# Returns the list of columns of a table.
# $1: table
get_columns (){
	local h out tmp i
	___check_db
	h=$(sqlite3 $SQLFILE "PRAGMA table_info($1)")
	if [ $(echo $h | wc -w) -eq 0 ]; then
		echo "(sqlite.sh) ERROR: Table \'$1\' did not exist!" >&2
		exit 1
	fi
	out=''
	for i in $( seq 1 $(echo $h| wc -w) ); do
		tmp=$(echo $h | cut -d' ' -f$i | cut -d'|' -f2)
		out="$out $tmp"
	done
	echo $out
}

# Create Config-Variable-Table
# $1: sql table
cvt_create() {
	local h
	___check_db
	h=$(sqlite3 $SQLFILE "CREATE  TABLE 'main'.\'$1\' ('Name' TEXT PRIMARY KEY  NOT NULL  UNIQUE , 'Value' TEXT)" 2>&1)
	if [ $? -ne 0 ]; then
		echo "(sqlite.sh) ERROR: Config-Variable-Table \'$1\' could not be created!" >&2
		echo "(sqlite.sh) -> $h" >&2
		exit 1
	else
		[ $VERBOSE ] && echo "(sqlite.sh) INFO: Config-Variable-Table \'$1\' created!" >&2
	fi	
}

# INPUT FUNCTIONS -------------------------------------------------------------

# Insert variables as a new dataset.
# $1: sql table
# $2: prefix
insert (){
	local cols col val vals h var
	cols=$(get_columns $1)
	pf="$2_"
	for col in $cols; do
		eval val="\\$${pf}$col"
		vals="$vals \'$val\'"
	done
	vals=$(echo $vals|sed "s/ /,/g")
	h=$(sqlite3 $SQLFILE "INSERT INTO $1 VALUES($vals)" 2>&1)
	if [ $? -ne 0 ]; then
		echo "(sqlite.sh) -> $h" >&2
		exit 1
	fi
	[ $VERBOSE ] && echo "(sqlite.sh) INFO: Add data set ($vals) to \'$1\'!" >&2
}

# Update the value of a column at each row matching the where-statement.
# $1: sql table
# $2: column-name
# $3: value
# $4: where statement
update (){
	local h
	___check_db
	h=$(sqlite3 $SQLFILE "UPDATE $1 SET $2=\'$3\' WHERE $4" 2>&1)
	if [ $? -ne 0 ]; then
		echo "(sqlite.sh) -> $h" >&2
		exit 1
	fi
	[ $VERBOSE ] && echo "(sqlite.sh) INFO: Update data ($2=\'$3\') in \'$1\' where $4!" >&2
}

# Add or Replace a variable in Config-Variable-Table
# $1: sql table
# $2: name
# $3: value
cvt_set() {
	___check_db
	h=$(sqlite3 $SQLFILE "INSERT OR REPLACE INTO $1 VALUES(\'$2\',\'$3\')" 2>&1)
	if [ $? -ne 0 ]; then
		echo "(sqlite.sh) -> $h" >&2
		exit 1
	else
		[ $VERBOSE ] && echo "(sqlite.sh) INFO: Set $2=\'$3\' in \'$1\'!" >&2
	fi	
}

# OUTPUT FUNCTIONS -------------------------------------------------------------

# Call a function for each data row in a sql table and
# providing the respective values for each call.
# $1: sql table
# $2: function
# $3: prefix for variables
# $4: order by column
func (){
	local sql cols line val n
	cols=$(get_columns $1)
	sql="SELECT * FROM $1"
	[ ! "$4" = '' ] && sql="$sql ORDER BY $4"
	sqlite3 $SQLFILE "$sql;" | while read line; do
		vals=$(echo $line | sed "s/|/ /g")
		___unsetlist "$cols" $3
		___deflist "$cols" "$vals" $3
		$2
	done 
}

# Call a function for each data row in a sql table and
# providing the respective values for each call.
# $1: sql table
# $2: function
# $3: prefix for variables
# $4: where statement
# $5: order by column
func_where (){
	local sql cols line val n
	cols=$(get_columns $1)
	sql="SELECT * FROM $1 WHERE $4"
	[ ! "$5" = '' ] && sql="$sql ORDER BY $5"
	sqlite3 $SQLFILE "$sql;" | while read line; do
		vals=$(echo $line | sed "s/|/ /g")
		___unsetlist "$cols" $3
		___deflist "$cols" "$vals" $3
		$2
	done 
}

# Returns the data of a given sql row.
# $1: sql table
# $2: where statement
# $3: prefix for variables
row (){
	local cols res vals
	cols=$(get_columns $1)
	___unsetlist "$cols" $3
	res=$(sqlite3 $SQLFILE "SELECT * FROM $1 WHERE $2" 2>&1)
	if [ $? -ne 0 ]; then
		echo "(sqlite.sh) -> $res" >&2
		exit 1
	fi
	[ $VERBOSE ] && [ $(echo $res | wc -w) -gt 1 ] && \
	echo "(sqlite.sh) INFO: Found more than 1 matching data set, return the 1st!"
	if [ $(echo $res | wc -w) -gt 0 ]; then
		vals=$(echo $res | cut -d' ' -f1 | sed "s/|/ /g")
		___deflist "$cols" "$vals" $3
	else
		[ $VERBOSE ] && echo "(sqlite.sh) INFO: Found no matching data set!"
	fi
}

# Read a Config-Variable-Table
# $1: sql table
# $2: prefix for variables
cvt_read() {
	local res tmp i
	___check_db
	res=$(sqlite3 $SQLFILE "SELECT * FROM $1" 2>&1)
	if [ $? -ne 0 ]; then
		echo "(sqlite.sh) -> $res" >&2
		exit 1
	fi
	names=''; vals=''
	for i in $( seq 1 $(echo $res| wc -w) ); do
		tmp=$(echo $res | cut -d' ' -f$i)
		n=$(echo $tmp | cut -d'|' -f1)
		v=$(echo $tmp | cut -d'|' -f2)
		names="$names $n"; vals="$vals $v"	
	done
	[ $VERBOSE ] && echo "(sqlite.sh) INFO: Read CVT!"
	names=$(___cll "$names"); vals=$(___cll "$vals")
	___deflist "$names" "$vals" $2
}
